Query by Form
Description
This page examiones ordering records, selecting records, querying by form, querying with multiple forms, querying by form syntax, and saving and opening queries.
Ordering Records
With Query by Form you order records based on the contents of one to five different fields. For example, using Query by Form you can sort customer records by State, then City, then Lastname, and finally Firstname. You specify record-ordering criteria in Order Criteria view, where number boxes appear next to the form fields. To order records in your form:
Select Query > Query by Form... or click .
Click the 'A..Z' button on the toolbar.
Click the box at the upper left corner of each field to assign its sorting sequence. For example, click on the State field box to assign the number 1 and designate the State field as the first (most important) criteria for ordering records. Clicking on subsequent order number boxes for other fields assigns the numbers 2, 3, 4, and 5, indicating the hierarchy for ordering records.
Optionally, click the arrows next to the numbers to specify ascending (up arrow) or descending (down arrow) order. Click Run Query any time to perform the query. Order Number Boxes:
Click the Clear Criteria button at any time to reset ordering.
Quick Run Query to display the first sorted record.
Click the following to view the other sorted records.
Selecting Records
Query by Form makes record selection easy. To find records that match specific criteria, you type the criteria directly in the fields of the form. For example, if you are viewing a table of customers and you want to view only customers from Massachusetts, you would type MA (the value for which you are looking) in the State field and run the query. To select records using your form:
Select Query > Query by Form... or click this.
Click the 'funnel' icon on the toolbar.
Optionally, if you understand filter syntax, enter filter criteria directly into the form fields.
1. You can specify multiple conditions; for example, you can type "MA" in the State field and "Springfield" in the City field. This query selects only customers who live in Springfield, Massachusetts.
2. You can also use query operators, such as a comma, when specifying conditions. The comma is used as a logical OR. For example, by entering "NY,MA" in the State field, Alpha Anywhere selects customers who live in New York or Massachusetts.
Optionally, for assistance is entering filters, click the 'lamp' icon to display the QBF Helper. Query by Form has a special syntax and using the QBF Helper helps you learn it. You can use the QBF Helper on any of the form fields.
1. Click in a form field and then click one of the expression buttons in the QBF Helper.
2. The QBF Helper prompts you for a value to finish the expression.
3. Alpha Anywhere fills the resulting expression in the form field.
Optionally, use any Alpha Anywhere function as Part of your search criteria. For example, the SOUNDEX() function allows for spelling variations. In a form with a LASTNAME field, you could enter SOUNDEX(LASTNAME)=SOUNDEX("Johnson") to search for last names of "Johnson" and spelling variations of "Johnson".
Querying with Multiple Forms
When creating elaborate OR queries, you can use multiple forms for the query. To move to additional query condition pages, click the following to view the other forms to use in the query. Alternatively, press the Page Up or Page Down keys on your keyboard. When the query runs, Alpha Anywhere will return records satisfying conditions on all the forms.
Optionally, click the Include Records '=' button on the toolbar. The query will return only records that match the search criteria.
Optionally, click the Exclude Records button on the toolbar. The query will return only records that do not match the search criteria.
Optionally, click the Show Expressions button on the toolbar to see the Xbasic expressions that you are using in your query.
Click Run to apply the filter and to display the first record that matches the query criteria.
Click the |<, <, >, >> buttons to view the other records that satisfy the query.
Click Clear to clear all filter criteria.
Click the following on the toolbar to show all records.
Query by Form Syntax
<
less than
Example:
<7 <4/16/2002
less than seven occurs before "Fred" (alphabetically) occurs before the date 4/16/2002
>
greater than
Example:
greater than seven occurs after "Fred" occurs after 4/16/2002
>7 >Fred >4/16/2002
<>
not equal to
Example:
not equal to seven not "Fred" does not occur on 4/16/2002
<>7 <>Fred <>4/16/2002
>=
greater than or equal to
Example:
is greater than or equal to seven is "Fred" or comes after "Fred" occurs on or after 4/16/2002
>=7 >=Fred >=4/16/2002
<=
less than or equal to
Example:
is less than or equal to seven is "Fred" or comes before "Fred" occurs on or before 4/16/2002
<=7 <=Fred <=4/16/2002
*
wildcard character
Example:
value starts with "fr" value ends with "ed"
fr* *ed
, (comma)
logical OR
Example:
value equals "car" or "truck"
car, truck
..
between
Example:
is between one and seven is between A and M occurs between 4/16/2001 and 4/16/2002
1..7 A..M 4/16/2001..4/16/2002
is blank
is blank
Example:
the field is empty
is blank
is not blank
is not blank
Example:
the field is not empty
is not blank
$
contains
Example:
the field contains the word "software" anywhere within the field
$ software
Saving and Opening Queries
Alpha Anywhere lets you save queries you create in Query-by-Form for future use. To save a query:
Define your search criteria and click the Save button on the toolbar.
Alpha Anywhere prompts you to name the query and saves it.
Saved queries appear on the Operations tab in the Control Panel.
To open a saved query:
Click the Open button on the toolbar.
Alpha Anywhere prompts you for the location of the query and opens it.
To create a new query:
Click the New button on the toolbar.
Alpha Anywhere opens a new, blank query form.
To remove saved queries place the following Xbasic statement under the OnPush event of a button:
dim tbl as P tbl = table.current() tbl.query_detach_all()
See Also